#!/bin/bash

if [ -n "$1" ]; then
    data_date=$1
else
    data_date=`date -d "-1 days" +%F`
fi

TMP_DWD_ADS_EVENT_LOG_TRAFFIC_SQL="
create table if not exists jtp_ads_warehouse.tmp_dwd_ads_event_log_traffic
as
    with tmp_ip as (
        select
          distinct  client_ip
        from (
        select client_ip
                   , ads_id
                   , event_time
                   , count(1) over (
                partition by client_ip, ads_id order by CAST(event_time as bigint)
                range between 300000 preceding and current row
                ) as cnt
              from jtp_ads_warehouse.tmp_dwd_ads_event_log_parse) t1

        where t1.cnt > 100

        union

        select
            distinct client_ip
        from (select client_ip
                   , ads_id
                   , event_time
                   , next_event_time
                   , (next_event_time - event_time) as interval_ms
              from (select client_ip
                         , ads_id
                         , event_time
                         , lead(event_time, 1, 0) over (
                      partition by client_ip, ads_id
                      order by CAST(event_time as bigint)
                      ) as next_event_time
                    from jtp_ads_warehouse.tmp_dwd_ads_event_log_parse) t1) t2

        having count(1) > 5
    ),
        tmp_device as (
            select
                distinct client_device_id
            from (select client_device_id
                       , ads_id
                       , event_time
                       , count(1) over (
                    partition by client_ip, ads_id
                    order by CAST(event_time as bigint)
                    range between 300000 preceding and current row
                    ) as cnt
                  from jtp_ads_warehouse.tmp_dwd_ads_event_log_parse) t1
            where t1.cnt > 100

            union

            SELECT DISTINCT client_device_id
-- client_device_id, ads_id, interval_ms, count(1) AS cnt
            FROM (
                     SELECT client_device_id
                          , ads_id
                          , event_time
                          , next_event_time

                          , (next_event_time - event_time) AS interval_ms
                     FROM (
                              SELECT client_device_id
                                   , ads_id
                                   , event_time
                                   , lead(event_time, 1, 0)
                                          over (PARTITION BY client_device_id, ads_id ORDER BY event_time) AS next_event_time
                              FROM jtp_ads_warehouse.tmp_dwd_ads_event_log_parse) t1) t2
            having count(1) > 5
        )
select
    t1.event_time,
    t1.event_type,
    t1.ads_id,
    t1.ad_name,
    t1.ads_product_id,
    t1.ads_product_name,
    t1.ads_product_price,
    t1.ads_material_id,
    t1.ads_group_id,
    t1.platform_id,
    t1.platform_name_en,
    t1.platform_name_zh,
    t1.client_country,
    t1.client_area,
    t1.client_province,
    t1.client_city,
    t1.client_ip,
    t1.client_device_id,
    t1.client_os_type,
    t1.client_os_version,
    t1.client_browser_type,
    t1.client_browser_version,
    t1.client_user_agent
        , t2.client_ip IS NULL AND t3.client_device_id IS NULL AS is_invalid_traffic
    from jtp_ads_warehouse.tmp_dwd_ads_event_log_browser t1
left join tmp_ip t2 on t1.client_ip = t2.client_ip
left join tmp_device t3 on t1.client_device_id = t3.client_device_id;
"

DWD_ADS_EVENT_LOG_INC_SQL="
insert overwrite table jtp_ads_warehouse.dwd_ads_event_log_inc partition (dt = '${data_date}')
select
       cast(event_time as bigint)
       ,event_type
       ,ads_id
       ,ad_name
       ,ads_product_id
       ,ads_product_name
       ,ads_product_price
       ,ads_material_id
       ,ads_group_id
       ,platform_id
       ,platform_name_en
       ,platform_name_zh
       ,client_country
       ,client_area
       ,client_province
       ,client_city
       ,client_ip
       ,client_device_id
       ,client_os_type
       ,client_os_version
       ,client_browser_type
       ,client_browser_version
       ,client_user_agent
       ,is_invalid_traffic
from jtp_ads_warehouse.tmp_dwd_ads_event_log_traffic;
"

/opt/module/spark/bin/beeline -u jdbc:hive2://node101:10001 -n bwie -e "${TMP_DWD_ADS_EVENT_LOG_TRAFFIC_SQL}${DWD_ADS_EVENT_LOG_INC_SQL}"